﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class PartnerEntity
    {
        public Int32 Id { get; set; }
        public Int32 Mem_Id { get; set; }
        public Int32 Income { get; set; }
        public Int32  Existing { get; set; }
		
        public DateTime DOB { get; set; }
		
        public String P_Name { get; set; }
        public String Education { get; set; }
        public String Id_Num { get; set; }
        public String P_Job { get; set; }
        public String Job_Place { get; set; }
        public String Job_Tittle { get; set; }
        public String POB { get; set; }
        public String TypeOfExist { get; set; }
		
        
        public Int32 Age
        {
            get
            {
                return DateTime.Now.Year - DOB.Year;
            }

        }


    }
    class Partner
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (System.Reflection.Assembly.GetExecutingAssembly().Location).Remove((System.Reflection.Assembly.GetExecutingAssembly().Location).IndexOf("Members.exe"))+ "Members.accdb;Jet OLEDB:Database Password=Yarabe11";
        private static string cmdText = "";



        public static PartnerEntity GetPartnerByMemId(int Mem_Id)
        {
            PartnerEntity c = null;


            cmdText = "select * from Partner where Mem_Id=?";
            OleDbConnection con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Mem_Id", Mem_Id);
            try
            {
                con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("PartnerEntity.GetClient()==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return c;
        }


        public static List<PartnerEntity> GetAllPartners()
        {
            PartnerEntity c = null;
            List<PartnerEntity> clients = new List<PartnerEntity>();

            cmdText = "select * from Partner ";
            OleDbConnection con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);

            try
            {
                con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                    clients.Add(c);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("PartnerEntity.GetAllClients()==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return clients;

        }




        public static bool Add(PartnerEntity c)
        {
            bool flag = true;
            cmdText = "insert into Partner (Mem_Id,Income,Existing,Job_Tittle,P_Name,Education,Id_Num,P_Job,Job_Place,DOB,POB,TypeOfExist)values(?,?,?,?,?,?,?,?,?,?,?,?)";

            OleDbConnection con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Mem_Id", c.Mem_Id);
            cmd.Parameters.AddWithValue("@Income", c.Income);
            cmd.Parameters.AddWithValue("@Existing", c.Existing);
            cmd.Parameters.AddWithValue("@Job_Tittle", c.Job_Tittle);
            cmd.Parameters.AddWithValue("@P_Name", c.P_Name);
            cmd.Parameters.AddWithValue("@Education", c.Education);
            cmd.Parameters.AddWithValue("@Id_Num", c.Id_Num);
            cmd.Parameters.AddWithValue("@P_Job", c.P_Job);
            cmd.Parameters.AddWithValue("@Job_Place", c.Job_Place);
            cmd.Parameters.AddWithValue("@DOB", c.DOB);
            cmd.Parameters.AddWithValue("@POB", c.POB);
            cmd.Parameters.AddWithValue("@TypeOfExist", c.TypeOfExist);
            try
            {
                con.Open();
                Int32 numOfRow = cmd.ExecuteNonQuery();
                if (numOfRow == 0)
                    flag = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى البيانات " + ex.Message);
                flag = false;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;

        }





        public static bool Update(PartnerEntity c)
        {
            Boolean flag = false;
            cmdText = "UPDATE Partner SET Mem_Id=?,Income=?,Existing=?,Job_Tittle=?,P_Name=?,Education=?,Id_Num=?,P_Job=?,Job_Place=?,DOB=?,POB=?,TypeOfExist=? WHERE Id=?";

            OleDbConnection con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Mem_Id", c.Mem_Id);
            cmd.Parameters.AddWithValue("@Income", c.Income);
            cmd.Parameters.AddWithValue("@Existing", c.Existing);
            cmd.Parameters.AddWithValue("@Job_Tittle", c.Job_Tittle);
            cmd.Parameters.AddWithValue("@P_Name", c.P_Name);
            cmd.Parameters.AddWithValue("@Education", c.Education);
            cmd.Parameters.AddWithValue("@Id_Num", c.Id_Num);
            cmd.Parameters.AddWithValue("@P_Job", c.P_Job);
            cmd.Parameters.AddWithValue("@Job_Place", c.Job_Place);
            cmd.Parameters.AddWithValue("@DOB", c.DOB);
            cmd.Parameters.AddWithValue("@POB", c.POB);
            cmd.Parameters.AddWithValue("@TypeOfExist", c.TypeOfExist);
            cmd.Parameters.AddWithValue("@Id", c.Id);

            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("PartnerEntity.Update==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }

        /*
           Delete using Mem_Id or Serial 
        */

        public static bool DeleteByMemId(Int32 Mem_Id)
        {
            Boolean flag = false;
            cmdText = "Delete from Partner WHERE Mem_Id=?";


            OleDbConnection con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Mem_Id", Mem_Id);
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                flag = true;


            }
            catch (Exception ex)
            {
                MessageBox.Show("PartnerEntity.Delete==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

            }
            return flag;
        }

       private static PartnerEntity fillEntityFromReader(OleDbDataReader reader)
        {
            PartnerEntity c = new PartnerEntity();
            c.Id = Int32.Parse(reader["Id"].ToString());

            c.Mem_Id = Int32.Parse(reader["Mem_Id"].ToString());
            if (reader["Income"] != null)
                c.Income = Int32.Parse(reader["Income"].ToString());
            if (reader["P_Name"] != null)
                c.P_Name = reader["P_Name"].ToString();
            if (reader["Education"] != null)
                c.Education = reader["Education"].ToString();
            if (reader["Job_Place"] != null)
                c.Job_Place = reader["Job_Place"].ToString();
            if (reader["POB"] != null)
                c.POB = reader["POB"].ToString();
            if (reader["TypeOfExist"] != null)
                c.TypeOfExist = reader["TypeOfExist"].ToString();
            if (reader["Existing"] != null)
                c.Existing = Int32.Parse(reader["Existing"].ToString());
            if (reader["Id_Num"] != null)
                c.Id_Num = reader["Id_Num"].ToString();
            if (reader["Job_Tittle"] != null)
                c.Job_Tittle = reader["Job_Tittle"].ToString();
            if (reader["P_Job"] != null)
                c.P_Job = reader["P_Job"].ToString();
            if (reader["DOB"] != null)
                c.DOB = (DateTime)reader["DOB"];
            
            return c;
        }


    }
}